package benchmarks.tpch.mysql.queries;

public class Q20MySQL extends GenericQueryMySQL {
    public Q20MySQL() {
        super.queryName = queryName;
        super.query = query;
    }
    public static final String queryName = "Q20";
    public static final String query =
            "select\n" +
            "\ts_name,\n" +
            "\ts_address\n" +
            "from\n" +
            "\tsupplier,\n" +
            "\tnation\n" +
            "where\n" +
            "\ts_suppkey in (\n" +
            "\t\tselect\n" +
            "\t\t\tps_suppkey\n" +
            "\t\tfrom\n" +
            "\t\t\tpartsupp\n" +
            "\t\twhere\n" +
            "\t\t\tps_partkey in (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\tp_partkey\n" +
            "\t\t\t\tfrom\n" +
            "\t\t\t\t\tpart\n" +
            "\t\t\t\twhere\n" +
            "\t\t\t\t\tp_name like 'forest%'\n" +
            "\t\t\t)\n" +
            "\t\t\tand ps_availqty > (\n" +
            "\t\t\t\tselect\n" +
            "\t\t\t\t\t0.5 * sum(l_quantity)\n" +
            "\t\t\t\tfrom\n" +
            "\t\t\t\t\tlineitem\n" +
            "\t\t\t\twhere\n" +
            "\t\t\t\t\tl_partkey = ps_partkey\n" +
            "\t\t\t\t\tand l_suppkey = ps_suppkey\n" +
            "\t\t\t\t\tand l_shipdate >= date '1994-01-01'\n" +
            "\t\t\t\t\tand l_shipdate < date '1994-01-01' + interval '1' year\n" +
            "\t\t\t)\n" +
            "\t)\n" +
            "\tand s_nationkey = n_nationkey\n" +
            "\tand n_name = 'CANADA'\n" +
            "order by\n" +
            "\ts_name";
}
